﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;

namespace QL_NOI_TRU.DataLayers
{
    class KetNoiHelper
    {

        #region 1. Khai báo các thành viên dữ liệu
        SqlConnection conn;
        SqlCommand cmd;
        #endregion

        #region 2. Các phương thức khởi tạo
        public KetNoiHelper()
        {
            conn = new SqlConnection(frmDangnhap.stringConn);

        }

        #endregion

        #region 3. Các phương thức thao tác với CSDL

        /// <summary>
        /// Phương thức mở kết nối tới CSDL
        /// </summary>
        private void Open()
        {
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();
        }

        /// <summary>
        /// Phương thức ngắt kết nối với CSDL
        /// </summary>
        private void Close()
        {
            if (conn.State == System.Data.ConnectionState.Open)
                conn.Close();
        }

        /// <summary>
        /// Phương thức lấy về một giá trị từ mệnh đề SELECT
        /// </summary>
        /// <param name="query">Câu lệnh SELECT</param>
        /// <returns>Một đối tượng</returns>
        public object GetValue(string query)
        {
            Open();
            cmd = new SqlCommand(query, conn);
            return cmd.ExecuteScalar();
        }

        public DataTable GetDateTable(string select)
        {
            SqlDataAdapter da = new SqlDataAdapter(select, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);

            return dt;
        }
        public int ExecuteNonQuery(string query)
        {
            Open();
            cmd = new SqlCommand(query, conn);
            return cmd.ExecuteNonQuery();
        }

        public void StorePicture(string filename)
        {
            byte[] imageData = null;
            // Read the file into a byte array
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                imageData = new Byte[fs.Length];
                fs.Read(imageData, 0, (int)fs.Length);
            }
            using (SqlConnection conn = new SqlConnection(frmDangnhap.stringConn))
            {
                SqlCommand cmd = new SqlCommand("InsertImage", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@MA_NV", filename);
                cmd.Parameters["@MA_NV"].Direction = ParameterDirection.Input;
                cmd.Parameters.Add("@HINHANH", SqlDbType.Image);
                cmd.Parameters["@HINHANH"].Direction = ParameterDirection.Input;
                // Store the byte array within the image field
                cmd.Parameters["@HINHANH"].Value = imageData;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public byte[] RetrieveImage()
{
byte[] imageData = null;
conn.Open();
SqlCommand cmd = new SqlCommand("select HINHANH from NHAN_VIEN", conn);
// Assume previously established command and connection
// The command SELECTs the IMAGE column from the table
 
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
reader.Read();
// Get size of image data – pass null as the byte array parameter
long bytesize = reader.GetBytes(0, 0, null, 0, 0);
// Allocate byte array to hold image data
imageData = new byte[bytesize];
long bytesread = 0;
int curpos = 0;
int chunkSize = 1;
while (bytesread < bytesize)
{
// chunkSize is an arbitrary application defined value
bytesread += reader.GetBytes(0, curpos, imageData, curpos, chunkSize);
curpos += chunkSize;
}
}
conn.Close();
// byte array ‘imageData’ now contains BLOB from database
return imageData;
}
        #endregion
    }
}